﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using schedulelist.ORM.dao.interfaces;
using schedulelist.ORM.domain;
using NHibernate;
using MySql.Data.MySqlClient;
namespace schedulelist.ORM.dao.classes
{
    class UserDAO : GenericDAO<User> , IUserDAO
    {
        public UserDAO(ISession session) : base(session) { }

        public User getUserByName(
        string name)
        {
            var list = session.CreateSQLQuery(
            "SELECT user.* FROM user" +
            " WHERE user.name='" + name + "'")
            .AddEntity("User", typeof(User))
            .List<User>();
            User t = new User();
            if (list.Count != 0)
            { return t = list[0]; }
            else
            { return t = null; }
        }

        public User getUserByLogin(
        string log)
        {
            var list = session.CreateSQLQuery(
            "SELECT user.* FROM user" +
            " WHERE user.login='" + log + "'")
            .AddEntity("User", typeof(User))
            .List<User>();
            User t = new User();
            if (list.Count!=0)
            { return t = list[0]; }
            else
            { return t = null; }
        }
        public User getUserById(
        int id)
        {
            var list = session.CreateSQLQuery(
            "SELECT user.* FROM user" +
            " WHERE user.Id='" + id + "'")
            .AddEntity("User", typeof(User))
            .List<User>();
            User t = new User();
            return list[0];
        }
        public IList<User> getUsersByRole(
        Role role)
        {
            var list = session.CreateSQLQuery(
            "SELECT user.* FROM user" +
            " WHERE user.RoleId='" + role.Id + "'")
            .AddEntity("User", typeof(User))
            .List<User>();
            return list; 
        }
        public IList<User> getUsersByDep(
        Departament d)
        {
            var list = session.CreateSQLQuery(
            "SELECT user.* FROM user" +
            " WHERE user.DepId='" + d.Id + "'")
            .AddEntity("User", typeof(User))
            .List<User>();
            return list;
        }
        public int GetTicsCountOfUser(User u)
        {
            //var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets" +
            //" WHERE user.name=" + sname + " AND tickets.ownerId=user.Id; ").AddEntity("Tickets", typeof(Tickets))
            //.List<Tickets>();
            var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets " +
            "WHERE tickets.ownerId =" + u.Id).AddEntity("Tickets", typeof(Tickets)).List<Tickets>();
            return list.Count;
        }

        public int GetCompletedTicsCountOfUser(User u)
        {
            //var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets" +
            //" WHERE user.name=" + sname + " AND tickets.ownerId=user.Id; ").AddEntity("Tickets", typeof(Tickets))
            //.List<Tickets>();
            var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets " +
            "WHERE tickets.execId =" + u.Id).AddEntity("Tickets", typeof(Tickets)).List<Tickets>();
            return list.Count;
        }

        public int GetTicsOfownByDate(User u,string datas)
        {
            //var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets" +
            //" WHERE user.name=" + sname + " AND tickets.ownerId=user.Id; ").AddEntity("Tickets", typeof(Tickets))
            //.List<Tickets>();
            var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets "+
            "WHERE tickets.ownerId=" + u.Id + " AND tickets.dm like '%" + datas + "%';").AddEntity("Tickets", typeof(Tickets)).List<Tickets>();
            return list.Count;
        }

        public int GetTicsOfExByDate(User u, string datas)
        {
            //var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets" +
            //" WHERE user.name=" + sname + " AND tickets.ownerId=user.Id; ").AddEntity("Tickets", typeof(Tickets))
            //.List<Tickets>();
            var list = session.CreateSQLQuery("SELECT tickets.* FROM tickets " +
            "WHERE tickets.execId=" + u.Id + " AND tickets.dd like '%" + datas + "%';").AddEntity("Tickets", typeof(Tickets)).List<Tickets>();
            return list.Count;
        }

    }
}
